﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Utils;
using DataHelper;
namespace DAL
{
    public class SalaryTableDao
    {
        SetsItemDao setsItemDao = new SetsItemDao();

        //获取该条工资的ID
        public string GetSalID(string empCode, string strYear, string strMonth)
        {
            return SQLHelper.ExecuteSqlGetNumber("Select top 1 salid from  Wage_SalaryList where EmpCode='" + empCode + "' and SalYear=" + strYear + " and SalMonth=" + strMonth);
        }
        //获取某个人某年某月的工资
        public DataTable GetOneInfo(string empCode, string strYear, string strMonth)
        {
            string strTable = " View_SalaryList ";
            //if (IsExistHistory(strYear, strMonth)) strTable = " Wage_SalaryListEnd ";
            return SQLHelper.ExecuteSqlGetDrToDt("select * from  " + strTable + " where EmpCode='" + empCode + "' and SalYear=" + strYear + " and SalMonth=" + strMonth);
        }
        //删除某人某年某月的工资
        //月结之后的不能删除
        public bool DeleteOneInfo(string empCode, string strYear, string strMonth)
        {
            return SQLHelper.ExecuteSql("delete from Wage_SalaryList where EmpCode='" + empCode + "' and SalYear=" + strYear + " and SalMonth=" + strMonth);
        }
        public bool IsExistOneInfo(string empCode, string strYear, string strMonth)
        {
            DataTable dt = GetOneInfo(empCode, strYear, strMonth);
            if (dt.Rows.Count > 0) return true;
            else
                return false;
        }
        //某年某月的工资簿是否存在
        public bool IsExistSLTable(string strYear, string strMonth, string setsID)
        {
            DataTable dt = GetLists("", strYear, strMonth, setsID);
            if (dt.Rows.Count >= 3)
                return true;
            else//否则
                return false;
        }
        #region 生成工资簿
        //删除某年某月的工资簿
        public bool DeleteSLTable(string strYear, string strMonth, string setsID)
        {
            return SQLHelper.ExecuteSql("delete from Wage_SalaryList where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
        }
        //生成工资簿
        public bool CreateLists(SqlParameter[] coll)
        {
            return SQLHelper.ExecutePorcedure("spJimCreateSLTable", coll);
        }

        #endregion
        //获取已经生成工资的 上一个，下一个员工编号
        //用在编辑已经生成的工资
        public string GetNextOrPre(string strYear, string strMonth, string empCode, string isNext)
        {
            if (isNext == "Next")
                return SQLHelper.ExecuteSqlGetNumber("select top 1 EmpCode from View_SalaryList where salYear=" + strYear + " and salMonth=" + strMonth + " and EmpCode>'" + empCode + "' order by EmpCode Asc");
            else
                return SQLHelper.ExecuteSqlGetNumber("select top 1 EmpCode from View_SalaryList where salYear=" + strYear + " and salMonth=" + strMonth + " and EmpCode<'" + empCode + "' order by EmpCode DESC");
        }
        //打印工资条
        public DataTable GetLists(string strYear, string strMonth, string setsID)
        {
            // DateTime dTime = System.DateTime.Now.AddMonths(-2); //两个月前离职的人员，将不能再生成工资了
            // string cYear = dTime.Year.ToString();
            // string cMonth = dTime.Month.ToString();

            string itemFields = setsItemDao.GetFieldsDic(setsID);
            //if (IsExistHistory(strYear, strMonth)) strTable = " Wage_SalaryListEnd ";
            string mainField = XmlUtility.ReadFiled("SalaryList_Slip");
            string[] mainArray = mainField.Split('|');
            StringBuilder showFields = new StringBuilder(mainArray[0]);
            showFields.Append(itemFields);
            showFields.Append(mainArray[1]);
            //string showFields = " DeptName as 部门,EmpCode as 工号,EmpName as 姓名 ,DutyName as 职务 " + setsItemDao.GetFieldsDic(setsID) + ",CONVERT(varchar(100), JoinTime, 23) as 入职时间,Memo as 备注";
            //上个月及以前离职的人，工资项目状态全部更改为不可用

            StringBuilder builder = new StringBuilder();
            //打印工资条之后将不能再生成上月工资。两个月之前离职人员的工资项一斤关闭
            //  builder.Append("Update Wage_EmpItem set  Statu='N' Where Statu='Y' and EmpCode in (select EmpCode from Admin_Emp_Leave where Statu='Y' and Year(LeaveTime)=" + cYear + " and Month (LeaveTime)=" + cMonth + " );");
            //  builder.Append("Update Wage_EmpSets set  Statu='N' Where Statu='Y' and EmpCode in (select EmpCode from Admin_Emp_Leave where Statu='Y' and Year(LeaveTime)=" + cYear + " and Month (LeaveTime)=" + cMonth + " );");
            builder.Append("select " + showFields.ToString() + " from View_SalaryList where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " order by DeptName asc,EmpCode Asc");
            return SQLHelper.ExecuteSqlGetDrToDt(builder.ToString());
        }
        //生成工资界面
        public DataTable GetLists(string empCode, string strYear, string strMonth, string setsID)
        {
            string strTable = " View_SalaryList ";
            string itemFields = setsItemDao.GetFieldsDic(setsID);
            //if (IsExistHistory(strYear, strMonth)) strTable = " Wage_SalaryListEnd ";
            string mainField = XmlUtility.ReadFiled("View_SalaryList");
            string[] mainArray = mainField.Split('|');
            StringBuilder showFields = new StringBuilder(mainArray[0]);
            showFields.Append(itemFields);
            showFields.Append(mainArray[1]);

            string sumItemFields = setsItemDao.GetFieldsSumDic(setsID);
            string sumField = XmlUtility.ReadFiled("View_SalaryList_sum");
            string[] sumArray = sumField.Split('|');
            StringBuilder sumFields = new StringBuilder(sumArray[0]);
            sumFields.Append(sumItemFields);
            sumFields.Append(sumArray[1]);

            // string showFields1 = " DeptName as 部门,BankAccount as 银行账号,EmpCode as 工号,EmpName as 姓名,DutyName as 职务,StateName as 状态,CONVERT(varchar(10), JoinTime, 23) as 入职日期,CONVERT(varchar(10), LeaveTime, 23) as 离职日期,WorkDayInMonth as 出勤天数" + itemFields + ",Memo as 备注,ZipCode as 身份证 ";
            // string sumFields = " '总计' as 部门,'' as 银行账号,'' as 工号,'' as 姓名,cast(count(DutyName) as varchar(10)) as 职务,'' as 状态,'' as 入职日期,'' as 离职日期,null as 出勤天数" + itemFields + ",'' as 备注,' ' as 身份证 ";
            //string strSQL = string.Empty;
            StringBuilder strSQL = new StringBuilder();
            if (!string.IsNullOrWhiteSpace(empCode))
            {
                strSQL.Append("select * from (select " + showFields.ToString() + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ");
                strSQL.Append(" UNION select " + sumFields.ToString() + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b");
                //strSQL = "select * from (select " + showFields + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID +" and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ";
                // strSQL += " UNION select " + sumFields + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b";
            }
            else
            {
                strSQL.Append("select * from (select " + showFields.ToString() + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
                strSQL.Append(" UNION select " + sumFields.ToString() + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,工号 Asc");
                // strSQL = "select * from (select " + showFields + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID;
                //strSQL += " UNION select " + sumFields + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,工号 Asc";
            }
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());

        }
        //导出工资
        public DataTable GetLists(string empCode, string strYear, string strMonth, string setsID, string salIndex)
        {
            string strTable = " View_SalaryList ";
            string itemFields = setsItemDao.GetFieldsDic(setsID);
            //if (IsExistHistory(strYear, strMonth)) strTable = " Wage_SalaryListEnd ";
            string mainField = XmlUtility.ReadFiled("View_SalaryList");
            string[] mainArray = mainField.Split('|');
            StringBuilder showFields = new StringBuilder(mainArray[0]);
            showFields.Append(itemFields);
            showFields.Append(mainArray[1]);

            string sumItemFields = setsItemDao.GetFieldsSumDic(setsID);
            string sumField = XmlUtility.ReadFiled("View_SalaryList_sum");
            string[] sumArray = sumField.Split('|');
            StringBuilder sumFields = new StringBuilder(sumArray[0]);
            sumFields.Append(sumItemFields);
            sumFields.Append(sumArray[1]);

            // string showFields1 = " DeptName as 部门,BankAccount as 银行账号,EmpCode as 工号,EmpName as 姓名,DutyName as 职务,StateName as 状态,CONVERT(varchar(10), JoinTime, 23) as 入职日期,CONVERT(varchar(10), LeaveTime, 23) as 离职日期,WorkDayInMonth as 出勤天数" + itemFields + ",Memo as 备注,ZipCode as 身份证 ";
            // string sumFields = " '总计' as 部门,'' as 银行账号,'' as 工号,'' as 姓名,cast(count(DutyName) as varchar(10)) as 职务,'' as 状态,'' as 入职日期,'' as 离职日期,null as 出勤天数" + itemFields + ",'' as 备注,' ' as 身份证 ";
            //string strSQL = string.Empty;
            StringBuilder strSQL = new StringBuilder();
            if (!string.IsNullOrWhiteSpace(empCode))
            {
                strSQL.Append("select * from (select " + showFields.ToString() + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ");
                strSQL.Append(" UNION select " + sumFields.ToString() + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b");
                //strSQL = "select * from (select " + showFields + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID +" and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ";
                // strSQL += " UNION select " + sumFields + " from  " + strTable + " where  salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b";
            }
            else
            {
                if (salIndex == "Y")
                {
                    strSQL.Append("select * from (select " + showFields.ToString() + ",SalIndex from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
                    strSQL.Append(" UNION select " + sumFields.ToString() + ",'0' as SalIndex  from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by SalIndex ASC,EmpCode ASC");
                }
                else
                {
                    strSQL.Append("select * from (select " + showFields.ToString() + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
                    strSQL.Append(" UNION select " + sumFields.ToString() + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,工号 Asc");
                }
                // strSQL = "select * from (select " + showFields + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID;
                //strSQL += " UNION select " + sumFields + " from  " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,工号 Asc";
            }
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());

        }
        #region 查询历史工资表
        /// <summary>
        /// 查询历史工资表，导出液使用这个方法
        /// </summary>
        /// <param name="empCode">员工编码</param>
        /// <param name="deptName">部门名称</param>
        /// <param name="strYear">年</param>
        /// <param name="strMonth">月</param>
        /// <param name="setsID">帐套ID</param>
        /// <param name="typeID">类型ID</param>
        /// <returns></returns>
        public DataTable GetLists(string empCode, string deptName, string strYear, string strMonth, string setsID, string typeID)
        {
            string strTable = " View_SalaryList ";
            if (IsExistHistory(strYear, strMonth, setsID)) strTable = " Wage_SalaryListEnd ";
            // string strTable = " Wage_SalaryListEnd ";
            string itemFields = setsItemDao.GetFieldsDic(setsID);
            string mainField = XmlUtility.ReadFiled("View_SalaryList");//显示字段
            string[] mainArray = mainField.Split('|');
            StringBuilder showFields = new StringBuilder(mainArray[0]);
            showFields.Append(itemFields);
            showFields.Append(mainArray[1]);

            string sumField = XmlUtility.ReadFiled("View_SalaryList_sum");//求和字段
            string[] sumArray = sumField.Split('|');
            string sumItemFields = setsItemDao.GetFieldsSumDic(setsID);
            StringBuilder sumFields = new StringBuilder(sumArray[0]);
            sumFields.Append(sumItemFields);
            sumFields.Append(sumArray[1]);

            //string showFields = " DeptName as 部门,BankAccount as 银行账号,EmpCode as 工号,EmpName as 姓名,DutyName as 职务,StateName as 状态,CONVERT(char(10), JoinTime, 23) as 入职日期,CONVERT(char(10), LeaveTime, 23) as 离职日期,WorkDayInMonth as 出勤天数" + setsItemDao.GetFieldsDic(setsID) + ",Memo as 备注,ZipCode as 身份证";
            // string sumFields = " '总计' as 部门,'' as 银行账号,'' as 工号,'' as 姓名,cast(count(DutyName) as varchar(10)) as 职务,'' as 状态,'' as 入职日期,'' as 离职日期,null as 出勤天数" + setsItemDao.GetFieldsSumDic(setsID) + ",'' as 备注,'' as 身份证";
            //第一优先typeID           
            //string strSQL = string.Empty;
            StringBuilder strSQL = new StringBuilder();
            if (typeID != "0")
            {
                strSQL.Append("select * from ( select " + showFields.ToString() + ",SalIndex from " + strTable + " where EmpCode in (Select EmpCode From Wage_SalaryBook Where TypeID=" + typeID + ") and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
                strSQL.Append(" UNION select " + sumFields.ToString() + ",'0' as SalIndex  from " + strTable + " where EmpCode in (Select EmpCode From Wage_SalaryBook Where TypeID=" + typeID + ") and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,SalIndex ASC,工号 Asc");
                //strSQL = "select * from ( select " + showFields + " from " + strTable + " where EmpCode in (Select EmpCode From Wage_SalaryBook Where TypeID=" + typeID + ") and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID;
                //strSQL += " UNION select " + sumFields + " from " + strTable + " where EmpCode in (Select EmpCode From Wage_SalaryBook Where TypeID=" + typeID + ") and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b  order by 部门 asc,工号 Asc";
            }//第二优先 empCode
            else if (!string.IsNullOrWhiteSpace(empCode))
            {
                strSQL.Append("select * from (select " + showFields.ToString() + " from " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and  (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ");
                strSQL.Append(" UNION select " + sumFields.ToString() + " from " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and  (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b");
                // strSQL = "select * from (select " + showFields + " from " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and  (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ";
                // strSQL += " UNION select " + sumFields + " from " + strTable + " where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " and  (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ) b";
            }
            else //把管理人员的过滤掉
            {
                if (deptName == "请选择部门") deptName = "";
                strSQL.Append("select * from ( select " + showFields.ToString() + ",SalIndex from " + strTable + " where   DeptName like '%" + deptName + "%' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);//EmpCode not in (Select EmpCode From Wage_SalaryBook ) and
                strSQL.Append(" UNION select " + sumFields.ToString() + ",'0' as SalIndex  from " + strTable + " where  DeptName like '%" + deptName + "%' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b order by 部门 asc,SalIndex ASC,工号 Asc");// EmpCode not in (Select EmpCode From Wage_SalaryBook ) and
                //strSQL = "select * from ( select " + showFields + " from " + strTable + " where  EmpCode not in (Select EmpCode From Wage_SalaryBook ) and DeptName like '%" + deptName + "%' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID;
                //strSQL += " UNION select " + sumFields + " from " + strTable + " where  EmpCode not in (Select EmpCode From Wage_SalaryBook ) and DeptName like '%" + deptName + "%' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ") b order by 部门 asc,工号 Asc";
            }

            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }
        #endregion
        //重新计算公式
        public void ReCau(string setsID, string empCode, string strYear, string strMonth)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select WageItem,EnFormula,ItemType  from Wage_SetsItem where ItemType<>'输入项' and SetsID=" + setsID);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["ItemType"].ToString() == "计算项")
                {
                    if (!string.IsNullOrWhiteSpace(empCode))
                    {
                        string strSQL = "Update Wage_SalaryList Set " + dt.Rows[i]["WageItem"].ToString() + "=" + dt.Rows[i]["EnFormula"].ToString() + " where EmpCode='" + empCode + "' and SalYear=" + strYear + " and SalMonth=" + strMonth + " and SetsID=" + setsID;
                        SQLHelper.ExecuteSql(strSQL);
                    }
                    else
                    {
                        string strSQL = "Update Wage_SalaryList Set " + dt.Rows[i]["WageItem"].ToString() + "=" + dt.Rows[i]["EnFormula"].ToString() + " where  SalYear=" + strYear + " and SalMonth=" + strMonth + " and SetsID=" + setsID;
                        SQLHelper.ExecuteSql(strSQL);
                    }
                }
                else if (dt.Rows[i]["ItemType"].ToString() == "所得税项")
                { //计算所得税 用存储过程
                    SQLHelper.ExecuteSql("exec spJimCauTax " + setsID + "," + strYear + "," + strMonth + ",'" + empCode + "'");
                }

            }

        }

        //设置特殊小组工资簿，以便特殊显示、查询、导出、打印
        //就是管理人员 工资要特殊导出一个文件，不和别的混合一块
        public bool ConfigAddBooks(ArrayList empCodeArray, string typeID)
        {
            bool flag = true;
            if (empCodeArray.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (object empCode in empCodeArray)
                {
                    string code = empCode.ToString().Trim();
                    if (SQLHelper.ExecuteSqlGetDataSet("select * from Wage_SalaryBook where EmpCode='" + code + "'").Tables[0].Rows.Count > 0)
                        continue;
                    stringBuilder.Append("insert into Wage_SalaryBook (EmpCode,TypeID,Statu) values ('" + code + "'," + typeID + ",'Y');");
                }
                string strSQL = stringBuilder.ToString();
                if (!string.IsNullOrEmpty(strSQL))
                    flag = SQLHelper.ExecuteSql(stringBuilder.ToString());
                else
                    flag = false;
            }
            else
            {
                flag = false;

            }
            return flag;
        }
        public bool ConfigDelBooks(ArrayList empCodeArray)
        {
            bool flag = true;
            if (empCodeArray.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (object empCode in empCodeArray)
                {
                    string code = empCode.ToString().Trim();
                    stringBuilder.Append("Delete from Wage_SalaryBook  Where EmpCode='" + code + "';");
                }
                string strSQL = stringBuilder.ToString();
                if (!string.IsNullOrEmpty(strSQL))
                    flag = SQLHelper.ExecuteSql(stringBuilder.ToString());
                else
                    flag = false;
            }
            else
            {
                flag = false;
            }
            return flag;
        }

        public DataTable GetBooks(string typeID)
        {
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append("[DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [JoinTime],[SalaryState],[SetsName],[SetsID] ");
            strFilter.Append(" From [View_EmpSets] Where EmpCode in (Select EmpCode from Wage_SalaryBook where TypeID=" + typeID + " and Statu='Y')");

            strFilter.Append(" Order by DeptID ASC, EmpCode ASC");

            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());

        }

        //月结账(结账后不能再修改)

        //上一月 是否已经月结
        //月结后statu=Y
        public bool IsMonthEnd(string strYear, string strMonth, string setsID)
        {
            int y = Int32.Parse(strYear);
            int m = Int32.Parse(strMonth);
            m = m - 1;
            if (m == 0)
            {
                m = 12;
                y = y - 1;
            }
            //string strSQL1 = "Select *  from Wage_Salary ";
            string strSQL = "Select *  from Wage_Salary where Statu='N' and  SalYear= " + y.ToString() + " and SalMonth=" + m.ToString() + " and SetsID=" + setsID;
            if (SQLHelper.ExecuteSqlGetDataSet(strSQL).Tables[0].Rows.Count > 0)
            {
                return true;//存在未月结
            }
            return false;//已经月结
        }

        //月结
        public string ConfigMonthEnd(string strYear, string strMonth, string setsID)
        {
            //如果以前月有没有结账的，本月不能结账        
            if (IsMonthEnd(strYear, strMonth, setsID)) return "以前月有没有结账的，本月不能结账";

            //如果本月已经结账过了，不能再结账
            int mm = Int32.Parse(strMonth);
            mm++;
            if (!IsMonthEnd(strYear, mm.ToString(), setsID)) return "该月已经结账，不能再次结账";

            SqlParameter para0 = new SqlParameter("@theYear", SqlDbType.SmallInt);
            SqlParameter para1 = new SqlParameter("@theMonth", SqlDbType.TinyInt);
            SqlParameter para2 = new SqlParameter("@setsID", SqlDbType.Int);
            para0.Value = Int32.Parse(strYear);
            para1.Value = Int32.Parse(strMonth);
            para2.Value = Int32.Parse(setsID);

            SqlParameter[] spList = { para0, para1, para2 };

            //结账的时候，要把工资基本信息，放到Wage_SalaryListEnd中 保持各种基本信息不变，比如当时职位，部门等等
            //if (SQLHelper.ExecuteSql("update Wage_SalaryList Set Statu='N' where Statu='Y' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ";Update Wage_Salary set statu='Y'  where Statu='N' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + "; Insert into Wage_SalaryListEnd select * from View_SalaryList  where Statu='N' and salYear=" + strYear + " and salMonth=" + strMonth + "  and SetsID=" + setsID))
            if (SQLHelper.ExecutePorcedure("spJimCreateSLTableEnd", spList))
                return "月结成功";
            else
                return "月结失败";
        }

        //反结账
        public string ConfigReMonthEnd(string strYear, string strMonth, string setsID)
        {
            //如果以前月有没有结账的，本月不能结账
            if (IsExistHistory(setsID))
            {
                return "存在没有结账工资簿，本月不能反结账";
            }
            if (SQLHelper.ExecuteSqlWithTransaction("Update Wage_Salary set statu='N'  where salYear=" + strYear + " and salMonth=" + strMonth + "  and SetsID=" + setsID + "; Delete from Wage_SalaryListEnd Where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + ";update Wage_SalaryList Set Statu='Y' where Statu='N' and salYear=" + strYear + " and salMonth=" + strMonth + "  and SetsID=" + setsID))

                return "反月结成功";
            else
                return "反月结失败";
        }

        //批量改变工资，双薪，年薪等使用
        public bool AddSalary(string setsID, string strYear, string strMonth, string wageItem, string empRange, string isAdd, string modiWay, string num)
        {
            StringBuilder stringBuilder = new StringBuilder("update Wage_SalaryList Set ");
            stringBuilder.Append(wageItem + "=");
            if (isAdd == "No")
                stringBuilder.Append(wageItem + "-" + num);
            else//加
            {
                if (modiWay == "isThree")
                    stringBuilder.Append(wageItem + "*3");
                else if (modiWay == "isDouble")
                    stringBuilder.Append(wageItem + "*2");
                else//isNumber
                    stringBuilder.Append(wageItem + "+" + num);
            }
            //Statu='Y' 没有月结的才可以计算，否则不计算
            stringBuilder.Append(" where Statu='Y' and salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID);
            switch (empRange)
            {
                case "Manager":
                    stringBuilder.Append(" and  EmpCode in (select empcode from Wage_SalaryBook Where TypeID=1)");//1说明管理组必须存在，且ID=1
                    break;
                case "Emp":
                    stringBuilder.Append(" and  EmpCode not in (select empcode from Wage_SalaryBook Where TypeID=1)");//1说明管理组必须存在，且ID=1
                    break;
                default://all
                    break;
            }

            return SQLHelper.ExecuteSql(stringBuilder.ToString());
        }
        //修改银行代发格式
        public bool UpdateFileFormat(string fileFormat)
        {
            //检测格式，略
            return new DefDicDao().UpdateOptionValue("银行代发", fileFormat);
            // return SQLHelper.ExecuteSql("update Wage_Option set Memo='" + fileFormat + "' where [Name]='银行代发'");
        }
        //获取银行代发格式
        public string GetFileFormat()
        {
            return new DefDicDao().GetOptionValue("银行代发");

            // SQLHelper.ExecuteSqlGetNumber("select memo from Wage_Option where [Name]='银行代发'");
        }
        public string GetFileType()
        { return new DefDicDao().GetOptionValue("银行代发格式"); }
        //获取工资表数据，导出代发文件
        public DataTable GetBankList(string strYear, string strMonth, string setsID, string fileFormat, string fileType)
        {
            // bool flag=false;
            // if (fileFormat.StartsWith("序号")) flag = true; //需要序号
            //银行账号，姓名，年，月，身份证号，代发金额，入职日期
            Hashtable ht = new Hashtable();
            ht.Add("序号", "'+cast(ID  as varchar(10)) +'");
            ht.Add("银行账号", "'+cast(BankAccount  as varchar(30)) +'");
            ht.Add("姓名", "'+EmpName+'");
            ht.Add("年", "'+cast(SalYear  as varchar(10)) +'");
            ht.Add("月", "'+cast(SalMonth  as varchar(10)) +'");
            ht.Add("身份证号", "'+cast(ZipCode  as varchar(25)) +'");
            ht.Add("入职日期", "'+jointime+'");


            string str = "";
            foreach (System.Collections.DictionaryEntry objDE in ht)
            {
                str = fileFormat.Replace(objDE.Key.ToString(), objDE.Value.ToString());
                fileFormat = str;
            }
            Hashtable ht2 = new SetsItemDao().GetFieldsHashTable(setsID);
            foreach (System.Collections.DictionaryEntry objDE in ht2)
            {
                // str = fileFormat.Replace(objDE.Value.ToString(), "'+cast(" + objDE.Key.ToString() + "  as varchar(26)) +'");
                str = fileFormat.Replace(objDE.Value.ToString(), "'+" + objDE.Key.ToString() + " +'");
                fileFormat = str;
            }
            if (StringUtility.IncludeHZ(fileFormat))
            {
                LogHelper.WriteLog(fileFormat + ":格式错误，包含汉字");
                return null;
            }

            StringBuilder sql = new StringBuilder("select identity(int,1,1) as ID,* into #T_SalaryList  from View_SalaryList Where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " order by DeptName asc,EmpCode Asc;");
            if (fileType == "TXT")
                sql.Append("select " + fileFormat.Trim(new char[2] { '\'', '+' }) + " from #T_SalaryList;");
            else //EXCEL
            {
                string[] item = fileFormat.Split(',');
                StringBuilder bilder = new StringBuilder();
                foreach (string a in item)
                {
                    if (ht.ContainsValue(a.Trim()))
                        bilder.Append(a + ",");
                    else
                        bilder.Append("cast(round(" + a + ",1,1) as float),");
                }
                string strsql = bilder.ToString().TrimEnd(",".ToCharArray()).Replace("'+", " ").Replace("+'", " ");
                //fileFormat.Replace("'+"," ").Replace("+'"," ")
                sql.Append("select " + strsql + " from #T_SalaryList;");
            }
            sql.Append("drop table  #T_SalaryList;");
            //return SQLHelper.ExecuteSqlGetDrToDt("select " + fileFormat.Trim(new char[2] { '\'', '+' }) + " from View_SalaryList Where salYear=" + strYear + " and salMonth=" + strMonth + " and SetsID=" + setsID + " order by DeptName asc,EmpCode Asc");
            return SQLHelper.ExecuteSqlGetDrToDt(sql.ToString());
        }


        /// <summary>
        /// 在历史工资表里面，判断是否有该月工资(就是判断是否有月结过该月工资)
        /// </summary>
        /// <param name="strYear"></param>
        /// <param name="strMonth"></param>
        /// <param name="setsID"></param>
        /// <returns>true 就是有，已经月结过了 false就是没有月结过 </returns>
        public bool IsExistHistory(string strYear, string strMonth, string setsID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("Select * from Wage_Salary  Where Statu='Y' and salYear=" + strYear + " and salMonth=" + strMonth + "  and SetsID=" + setsID);
            if (dt == null) return false;
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        ///在所有月份中 判断是否存在没有月结的工资
        /// </summary>
        /// <param name="setsID"></param>
        /// <returns>true 存在没有月结的工资 false不存在</returns>
        public bool IsExistHistory(string setsID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("Select * from Wage_Salary  Where Statu='N'  and SetsID=" + setsID);
            if (dt == null) return false;
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        //执行SQL语句  主要是生成 wage_salary 存储年月
        public bool ExecuteSLSQL(string strSQL)
        {
            return SQLHelper.ExecuteSql(strSQL);
        }

        public DataTable GetLists()
        {
            return SQLHelper.ExecuteSqlGetDrToDt("select * from Wage_Salary order by SalYear DESC,SalMonth ASC");
        }


        /// <summary>
        /// 预算本月工资数目
        /// </summary>
        /// <param name="strYear">哪一年</param>
        /// <param name="strMonth">预测那一月，根据数据库计算出来的</param>
        /// <param name="setsID">帐套</param>
        /// <returns></returns>
        public DataTable SalaryTrend(string strYear, string setsID)
        {
            //根据年份 自动算下一个月
            string strMonth = string.Empty;
            DataSet maxds = SQLHelper.ExecuteSqlGetDataSet("select max(SalMonth) as maxMonth from Wage_Salary  where Statu='Y' and SalYear=" + strYear + " and SetsID=" + setsID);
            if (maxds != null)
            {
                DataTable maxdt = maxds.Tables[0];// .Rows[0][maxMonth].ToString()
                if (maxdt.Rows.Count > 0)
                    strMonth = maxdt.Rows[0]["maxMonth"].ToString();
            }
            //else
            //    strMonth
            if (string.IsNullOrEmpty(strMonth))
            {
                strMonth = "1";
            }
            else
            {
                int m = Int32.Parse(strMonth);
                m++;
                if (m > 12) strMonth = "1";
                else strMonth = m.ToString();
            }
            SqlParameter para0 = new SqlParameter("@strYear", SqlDbType.Char);
            SqlParameter para1 = new SqlParameter("@strMonth", SqlDbType.VarChar);
            SqlParameter para2 = new SqlParameter("@setsID", SqlDbType.VarChar);
            para0.Value = strYear;
            para1.Value = strMonth;
            para2.Value = setsID;

            SqlParameter[] spList = { para0, para1, para2 };
            DataSet ds = SQLHelper.ExecutePorcedureGetDataSet("spJimSalaryTrend", spList);
            if (ds == null) return null;
            else
                return ds.Tables[0];
        }

        public DataTable SalaryTrendMonth(string strYear, string strMonth, string setsID)
        {
            SqlParameter para0 = new SqlParameter("@strYear", SqlDbType.Char);
            SqlParameter para1 = new SqlParameter("@strMonth", SqlDbType.VarChar);
            SqlParameter para2 = new SqlParameter("@setsID", SqlDbType.VarChar);
            para0.Value = strYear;
            para1.Value = strMonth;
            para2.Value = setsID;
            SqlParameter[] spList = { para0, para1, para2 };
            DataSet ds = SQLHelper.ExecutePorcedureGetDataSet("spJimSalaryTrendMonth", spList);
            if (ds == null) return null;
            else
                return ds.Tables[0];
        }

    }
}
